#include "operatedb.h"
#include <QDebug>
#include <QMessageBox>
#include <QSqlError>
#include <QSqlQuery>

OperateDb::OperateDb(QObject *parent) : QObject(parent)
{
    m_db= QSqlDatabase::addDatabase("QMYSQL");//括号里是数据库的驱动
}

OperateDb::~OperateDb()
{
    m_db.close();
}

OperateDb &OperateDb::getInstance()
{
    static OperateDb instance;
    return instance;
}

void OperateDb::connect()
{
    m_db.setHostName("localhost");
    m_db.setDatabaseName("mydb0406");
    m_db.setPort(3306);//端口号
    m_db.setUserName("root");
    m_db.setPassword("0805");
    if(m_db.open()) {
        qDebug() <<"数据库连接成功";
    }
    else {
        QMessageBox::critical(0,"数据库错误", m_db.lastError().text());
    }

}

bool OperateDb::handleRegist(const char *name, const char *pwd)
{
    if(name == NULL || pwd == NULL){
        return false;
    }
    QString sql = QString("select * from user_info where name = '%1'").arg(name);
    qDebug() <<"handleRegist select user name sql: " << sql;
    QSqlQuery q;
    if(!q.exec(sql) || q.next()) { //判断有没有值用next()
        return false;
    }

    sql = QString("insert into user_info(name,pwd) values('%1','%2')").arg(name).arg(pwd);
    qDebug() <<"handleRegist insert user name sql: " << sql;
    return q.exec(sql);
}

bool OperateDb::handleLogin(const char *name, const char *pwd)
{
    if(name == NULL || pwd == NULL){
        return false;
    }
    QString sql = QString("select * from user_info where name = '%1' and pwd = '%2'").arg(name).arg(pwd);
    qDebug() <<"handleLogin select user name sql: " << sql;
    QSqlQuery q;
    if(!q.exec(sql) || !q.next()) { //判断有没有值用next()
        return false;
    }

    sql = QString("update user_info set online = 1 where name = '%1' and pwd = '%2'").arg(name).arg(pwd);
    qDebug() <<"handleLogin update user online sql: " << sql;
    return q.exec(sql);
}

void OperateDb::handleOffLine(const char *name)
{
    if(name == NULL){
        return;
    }
    QString sql = QString("update user_info set online = 0 where name = '%1'").arg(name);
    qDebug() <<"handleLogin update user online sql: " << sql;
    QSqlQuery q;
    q.exec(sql);
}

int OperateDb::handleFindUser(const char *name)
{//-1:用户不存在，0:离线，1:在线
    if(name == NULL){
        return -1;//用户不存在
    }
    QString sql = QString("select online from user_info where name = '%1';").arg(name);
    qDebug() <<"handleFindUser select user online sql: " << sql;
    QSqlQuery q;
    q.exec(sql);//执行q
    if(q.next()) {//判断q有没有结果
        return q.value(0).toInt();//返回记录   0代表取第一个字段  转类型
    }
    return -1;
}

QStringList OperateDb::handleOnlineUser()
{
    QString sql = QString("select name from user_info where online = 1");
    QSqlQuery q;
    q.exec(sql);
    QStringList result;//定义结果

    result.clear();
    while (q.next()) {//q存在
        qDebug() <<"handleOnlineUser" <<result;
        result.append(q.value(0).toString());//0代表取第一个字段  转类型
    }
    return result;
}

int OperateDb::handleAddFriend(const char *curName, const char *tarName)
{
    if(curName == NULL || tarName == NULL){//判空
        return -1;
    }
    //R"()"能换行
    QString sql = QString (R"(
                           select * from friend where
                           (
                             user_id = (select id from user_info where name = '%1')
                             and
                             friend_id = (select id from user_info where name = '%2')
                           )
                           or
                           (
                             friend_id = (select id from user_info where name = '%3')
                             and
                             user_id = (select id from user_info where name = '%4')
                           );
                           )").arg(curName).arg(tarName).arg(curName).arg(tarName);
    QSqlQuery q;
    q.exec(sql);//得到对象q
    if(q.next())//判断有没有值
    {
        return -2;//已经是好友
    }
    sql = QString("select online from user_info where name = '%1'").arg(tarName);//判断是否在线
    q.exec(sql);//得到对象q
    if(q.next())//判断有没有值
    {
        return q.value(0).toInt();//1在线， 0不在线  value(0)代表第一个
    }
    return -1;
}

void OperateDb::handleAddFriendAgree(const char *curName, const char *tarName)
{
    if(curName == NULL || tarName == NULL){//判空
        return;
    }
    //R"()"能换行
    QString sql = QString (R"(
                           insert into friend(user_id, friend_id)
                           select u1.id, u2.id from user_info u1, user_info u2
                           where u1.name = '%1'and u2.name = '%2'
                           )").arg(curName).arg(tarName);
    QSqlQuery q;
    q.exec(sql);//得到对象q
}

QStringList OperateDb::handleOnlineFriend(const char *name)
{
    QString sql = QString (R"(
                           select name from user_info where id in
                           (
                             select user_id from friend where friend_id = (select id from user_info where name = '%1')
                             union
                             select friend_id from friend where user_id = (select id from user_info where name = '%1')
                           )
                           and online = 1
                           )").arg(name);
    QSqlQuery q;//定义
    q.exec(sql);//执行
    QStringList result;
    result.clear();//清空
    qDebug() <<"QStringList OperateDb::handleOnlineFriend" << result;
    while (q.next()) {//判断q存在
        result.append(q.value(0).toString());//0代表取第一个字段  转类型
    }
    qDebug() <<"OperateDb::handleOnlineFriend" << result;
    return result;
}

bool OperateDb::handleDelFriend(const char *curName, const char *tarName)
{
    if(curName == NULL || tarName == NULL){//判空
        return false;
    }
    //R"()"能换行
    QString friendWhere = QString (R"(where
                           (
                             user_id = (select id from user_info where name = '%1')
                             and
                             friend_id = (select id from user_info where name = '%2')
                           )
                           or
                           (
                             friend_id = (select id from user_info where name = '%3')
                             and
                             user_id = (select id from user_info where name = '%4')
                           );
                           )").arg(curName).arg(tarName).arg(curName).arg(tarName);
    QString sql = QString("select * from friend %1").arg(friendWhere);//接上sql语句
    qDebug()<<"handleDelFriend" <<sql;
    QSqlQuery q;
    q.exec(sql);
    if(!q.next())
    {
        return false;//不是好友
    }
    sql = QString("delete from friend %1").arg(friendWhere);
    q.exec(sql);
    return true;
}
